<?php
/**
 * Created by PhpStorm.
 * User: zlkmu
 * Date: 2017/8/14
 * Time: 20:29
 */
    require_once ('../SQLBase.php');
    require_once ('../Common.php');
    require_once ('../BusinessApprove/BusinessApp.php');
    class LoanFee{
        //创建借款审批单,根据出差审批ID创建，
        function createLoanMoneyApproval($appID, $loanMoneyData){
            $conn = new SQLBase();
            $id = uniqid();
            $query = "INSERT loan_money(loan_money_ID, user_ID, section_ID, approval_ID, loan_money, loan_money_reason, write_time)
                     VALUES('$id','".$loanMoneyData['user_id']."','".$loanMoneyData['depart_id']."','".$appID."','".$loanMoneyData['money']."','".$loanMoneyData['reason']."',NOW())";
            $r = $conn->queryData($query);
            if($r){
                return $id;
            }
            else{
                return false;
            }
        }

        //辅助函数，获取用户普通借款数据(非详细信息)
        function getOrdinaryLoanFeeByUser($userID, $state)
        {
            $conn = new SQLBase();
            $sql = "SELECT loan_money.*, allsection.section_name, user.user_name FROM loan_money, allsection, user WHERE loan_money.user_ID = '$userID' 
                    AND approval_ID = '' AND loan_money.section_ID = allsection.section_ID AND loan_money.user_ID = user.user_ID AND loan_money.state = '$state'";
            $r = $conn->queryData($sql);
            $result_array = array();
            if ($r) {
                $result_array['state'] = 1;
                $result_array['state_error'] = '';
                for ($i = 0; $i < $r->num_rows; $i++) {
                    $row = $r->fetch_array();
                    $result_array['data'][$i] = array('user_name' => $row['user_name'], 'user_id' => $row['user_ID'], 'section_id' => $row['section_ID'], 'loan_money' => $row['loan_money'],
                        'app_time' => $row['write_time'], 'app_id' => $row['loan_money_ID'], 'section_name' => $row['section_name'], 'state'=>$row['state'], 'state_detail'=>$row['state_detail']);
                }
                $result_array['totalCount'] = $r->num_rows;
            } else {
                $result_array['state'] = 0;
                $result_array['state_error'] = 'DB_ERROR';
            }
            return $result_array;
        }

        //获取借款审批单具体信息，byApp = true ,appID为出差审批单ID， 否则为普通借款单ID
        function getLoanMoneyInfo($appID, $byApp = true){
            $conn = new SQLBase();
            if($byApp)
                $query = "SELECT loan_money.*, user.user_name, allsection.section_name FROM loan_money INNER JOIN user, allsection WHERE loan_money.approval_ID='$appID' 
                      AND loan_money.user_ID = user.user_ID AND loan_money.section_ID = allsection.section_ID AND is_dispose = 0";
            else
                $query = "SELECT loan_money.*, user.user_name, allsection.section_name FROM loan_money INNER JOIN user, allsection WHERE loan_money.loan_money_ID='$appID' 
                      AND loan_money.user_ID = user.user_ID AND loan_money.section_ID = allsection.section_ID AND is_dispose = 0";
            $r = $conn->queryData($query);
            if($r){
                if($r->num_rows > 0){
                    $row = $r->fetch_array();
                    $loanMoneyData = array('loan_money_id'=>$row['loan_money_ID'],
                        'user_id'=>$row['user_ID'],
                        'user_name'=>$row['user_name'],
                        'depart_id'=>$row['section_ID'],
                        'section_name'=>$row['section_name'],
                        'approval_ID'=>$row['approval_ID'],
                        'reason'=>$row['loan_money_reason'],
                        'money'=>$row['loan_money'],
                        'return_money'=>$row['return_money'],
                        'operator_ID'=>$row['operator_ID'],
                        'section_manager_ID'=>$row['section_manager_ID'],
                        'section_leader_ID'=>$row['section_leader_ID'],
                        'leader_ID'=>$row['leader_ID'],
                        'write_time'=>$row['write_time'],
                        'state'=>$row['state'],
                        'state_detail'=>$row['state_detail'],
                        'section_approve_opinion'=>$row['section_approve_opinion'],
                        'section_leader_opinion'=>$row['section_leader_opinion'],
                        'leader_opinion'=>$row['leader_opinion']);
                    return $loanMoneyData;
                }
                else{
                    return false;
                }
            }
            else{
                return false;
            }
        }

        //获取部门普通借款信息
        function getOrdinaryLoanFeeBySection($sectionID, $allsection, $state){
            if($allsection == 0){
                $sql = "SELECT loan_money.*, allsection.section_name, user.user_name FROM loan_money, allsection, user WHERE loan_money.section_ID = '$sectionID' AND loan_money.section_ID = allsection.section_ID 
                  AND loan_money.user_ID = user.user_ID AND loan_money.state_detail='$state' AND loan_money.state = '0' AND loan_money.approval_ID = '' AND is_dispose = 0";
            }
            else{
                $sql = "SELECT loan_money.*, allsection.section_name, user.user_name FROM loan_money, allsection, user WHERE loan_money.section_ID = allsection.section_ID 
                  AND loan_money.user_ID = user.user_ID AND loan_money.state_detail='$state' AND loan_money.state = '0' AND loan_money.approval_ID = '' AND is_dispose = 0 AND loan_money.section_ID IN (";
                for($i = 0; $i < count($sectionID); $i++){
                    $sql .= "'$sectionID[$i]'";
                    if($i != count($sectionID)-1){
                        $sql .= ",";
                    }
                    else{
                        $sql .= ")";
                    }
                }
            }
            $conn = new SQLBase();
            $result_array = array();
            $r = $conn->queryData($sql);
            if($r){
                $result_array['state'] = 1;
                $result_array['state_error'] = '';
                $result_array['data'] = array();
                for($i = 0; $i < $r->num_rows; $i++){
                    $row = $r->fetch_array();
                    $result_array['data'][$i] = array('type'=>2, 'user_name'=>$row['user_name'], 'user_id'=>$row['user_ID'], 'section_id'=>$row['section_ID'], 'loan_money'=>$row['loan_money'], 'app_time'=>$row['write_time'], 'app_id'=>$row['loan_money_ID'], 'section_name'=>$row['section_name']);
                }
                $result_array['total_count'] = $r->num_rows;
            }
            else{
                $result_array['state'] = 0;
                $result_array['state_error'] = 'DB_ERROR';
            }
            return $result_array;
        }

        //辅助函数，归还借款信息, 借款信息ID， 归还借款金额，操作用户ID
        function financeCheckLoanFee($loan_fee_id, $return_money, $user_id){
            $conn = new SQLBase();
            $sql = "UPDATE loan_money SET return_money = return_money + $return_money,  
                                           return_opertor_id = '$user_id' 
                                           WHERE loan_money_ID = '$loan_fee_id'";
            $r = $conn->queryData($sql);
            if(!$r){
                return false;
            }
            else{
                $sql = "UPDATE loan_money SET state = 3 WHERE loan_money_ID = '$loan_fee_id' AND return_money >= loan_money";

                $r = $conn->queryData($sql);
                if(!$r){
                    return false;
                }
                else{
                    return true;
                }
            }
        }

        //辅助函数，获取用户业务报销时候的借款信息
        function getLoanFeeIDByUser($userID, $sectionID){
            $conn = new SQLBase();
            $sql = "SELECT loan_money_ID, loan_money FROM loan_money WHERE user_ID = '$userID' AND section_ID = '$sectionID' AND approval_ID = '' AND state = 1";
            $r = $conn->queryData($sql);
            $result = array();
            if($r){
                for($i = 0; $i < $r->num_rows; $i++){
                    $row = $r->fetch_array();
                    $result[$i] = array('loan_money_ID'=>$row['loan_money_ID'], 'money'=>$row['loan_money']);
                }
                return $result;
            }
            else{
                return false;
            }
        }


        //批准某个借款审批，接受参数为appID,审批人ID，同意/拒绝，哪一级审批，审批内容, 类别（出差还是普通借款)
        function ApproveLoanMoney($appID, $userID, $type, $opinion, $opinion_detail, $is_business=true){
            $sql = '';
            $b_app = new BusinessApp();
            if($opinion == 1){
                switch($type){
                    case 1:
                        $sql = "UPDATE loan_money SET section_manager_ID='$userID', state_detail='02', section_approve_opinion='$opinion_detail'";
                        break;
                    case 2:
                        if($is_business && $b_app->isApproveInArea($appID)){
                            $sql = "UPDATE loan_money SET section_leader_ID='$userID', leader_ID='$userID', state='1', leader_opinion='$opinion_detail' 
                                  state_detail='11', section_leader_opinion='$opinion_detail'";
                        }
                        else{
                            $sql = "UPDATE loan_money SET section_leader_ID='$userID', state_detail='03', section_leader_opinion='$opinion_detail'";
                        }
                        break;
                    case 3:
                        $sql = "UPDATE loan_money SET leader_ID='$userID', state='1', state_detail='11', leader_opinion='$opinion_detail'";
                        break;
                }
            }
            else{
                switch($type){
                    case 1:
                        $sql = "UPDATE loan_money SET section_manager_ID='$userID', state='2', state_detail='21', section_approve_opinion='$opinion_detail'";
                        break;
                    case 2:
                        $sql = "UPDATE loan_money SET section_leader_ID='$userID', state='2', state_detail='22', section_leader_opinion='$opinion_detail'";
                        break;
                    case 3:
                        $sql = "UPDATE loan_money SET leader_ID='$userID', state='2', state_detail='23', leader_opinion='$opinion_detail'";
                        break;
                }
            }

            if($is_business){
                $sql .=  " WHERE approval_ID='$appID'";
            }
            else{
                $sql .=  " WHERE loan_money_ID='$appID'";
            }

            $conn = new SQLBase();

            $r = $conn->queryData($sql);
            if($r){
                return true;
            }else{
                return false;
            }

        }

        //辅助函数，获取所有未归还的借款数据
        function getAllLoanFee($sectionID, $username, $loan_id){
            $sql = "SELECT loan_money.*, allsection.section_name, user.user_name FROM loan_money, allsection, user 
                    WHERE loan_money.loan_money > loan_money.return_money AND loan_money.state = 1 
                    AND allsection.section_ID = loan_money.section_ID AND allsection.section_state = 1 
                    AND user.user_ID = loan_money.user_ID  AND is_dispose = 0";
            if($loan_id != ''){
                $sql .= " AND loan_money_ID = '$loan_id'";
            }
            else{
                if($username != ''){
                    $sql .= " AND user.user_name LIKE '$username%'";
                }
                if($sectionID != 0){
                    $sql .= " AND loan_money.section_ID = $sectionID";
                }
            }

            $conn = new SQLBase();
            $r = $conn->queryData($sql);
            if($r){
                $result_array['state'] = 1;
                $result_array['state_error'] = '';
                for($i = 0; $i < $r->num_rows; $i++){
                    $row = $r->fetch_array();
                    $result_array['data'][$i] = array('user_name' => $row['user_name'], 'user_id' => $row['user_ID'], 'section_id' => $row['section_ID'], 'loan_money' => $row['loan_money'],
                        'app_time' => $row['write_time'], 'app_id' => $row['loan_money_ID'], 'section_name' => $row['section_name'], 'return_money'=>$row['return_money']);
                }
                $result_array['totalCount'] = $r->num_rows;
            }
            else{
                $result_array['state'] = 0;
                $result_array['state_error'] = 'DB_ERROR';
            }
            return $result_array;
        }

    }




?>